const { executeQuery } = require('../config/dbConfig');

class StatisticsService {
  // 获取总体统计数据
  async getStatistics() {
    // 获取用户统计
    const [userStats] = await executeQuery(
      `SELECT 
        COUNT(*) as total_users,
        COUNT(CASE WHEN status = 1 THEN 1 END) as active_users,
        COUNT(CASE WHEN status = 0 THEN 1 END) as inactive_users,
        COUNT(CASE WHEN created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN 1 END) as new_users_month
       FROM users`
    );

    // 获取书籍统计
    const [bookStats] = await executeQuery(
      `SELECT 
        COUNT(*) as total_books,
        COUNT(CASE WHEN status = 1 THEN 1 END) as active_books,
        COUNT(CASE WHEN status = 0 THEN 1 END) as inactive_books
       FROM posts`
    );

    // 获取互动统计
    const [interactionStats] = await executeQuery(
      `SELECT 
        (SELECT COUNT(*) FROM reports) as total_reports,
        (SELECT COUNT(*) FROM reports WHERE status = 'pending') as pending_reports
       FROM reports LIMIT 1`
    );

    // 获取标签统计
    const [tagStats] = await executeQuery(
      `SELECT 
        COUNT(DISTINCT tags) as total_tags
       FROM posts 
       WHERE status = 1 
       AND tags IS NOT NULL 
       AND tags != ''`
    );

    // 获取热门标签
    const hotTags = await executeQuery(
      `SELECT 
        tags as tag,
        COUNT(*) as count
       FROM posts
       WHERE status = 1
       AND tags IS NOT NULL
       AND tags != ''
       GROUP BY tag
       ORDER BY count DESC
       LIMIT 5`
    );

    // 获取热门书籍
    const hotBooks = await executeQuery(
      `SELECT 
        p.id,
        p.title,
        p.like_count,
        p.comment_count
       FROM posts p
       WHERE p.status = 1
       ORDER BY (p.like_count + p.comment_count) DESC
       LIMIT 5`
    );

    // 获取活跃用户
    const activeUsers = await executeQuery(
      `SELECT 
        u.id,
        u.nickname,
        COUNT(DISTINCT p.id) as post_count,
        COUNT(DISTINCT c.id) as comment_count
       FROM users u
       LEFT JOIN posts p ON u.id = p.user_id
       LEFT JOIN comments c ON u.id = c.user_id
       WHERE u.status = 1
       GROUP BY u.id
       ORDER BY (COUNT(DISTINCT p.id) + COUNT(DISTINCT c.id)) DESC
       LIMIT 5`
    );

    return {
      users: userStats,
      books: bookStats,
      interactions: interactionStats,
      tags: {
        total: tagStats.total_tags,
        hot: hotTags
      },
      hotBooks,
      activeUsers
    };
  }

  // 获取用户统计
  async getUserStatistics() {
    const [userStats] = await executeQuery(
      `SELECT 
        COUNT(*) as total_users,
        COUNT(CASE WHEN status = 1 THEN 1 END) as active_users,
        COUNT(CASE WHEN status = 0 THEN 1 END) as inactive_users,
        COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as new_users_today,
        COUNT(CASE WHEN DATE(created_at) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 1 END) as new_users_week,
        COUNT(CASE WHEN DATE(created_at) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN 1 END) as new_users_month
       FROM users`
    );

    return userStats;
  }

  // 获取书籍统计
  async getBookStatistics() {
    const [bookStats] = await executeQuery(
      `SELECT 
        COUNT(*) as total_books,
        COUNT(CASE WHEN status = 1 THEN 1 END) as active_books,
        COUNT(CASE WHEN status = 0 THEN 1 END) as inactive_books,
        COUNT(CASE WHEN DATE(created_at) = CURDATE() THEN 1 END) as new_books_today,
        COUNT(CASE WHEN DATE(created_at) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN 1 END) as new_books_week,
        COUNT(CASE WHEN DATE(created_at) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) THEN 1 END) as new_books_month,
        (SELECT COUNT(DISTINCT user_id) FROM posts) as total_authors
       FROM posts`
    );

    return bookStats;
  }

  // 获取互动统计
  async getInteractionStatistics() {
    const [interactionStats] = await executeQuery(
      `SELECT 
        (SELECT COUNT(*) FROM likes) as total_likes,
        (SELECT COUNT(*) FROM comments) as total_comments,
        (SELECT COUNT(*) FROM ratings) as total_ratings,
        (SELECT COUNT(*) FROM reports WHERE status = 'pending') as pending_reports,
        (SELECT COUNT(*) FROM reports WHERE status = 'handled') as handled_reports,
        (SELECT COUNT(*) FROM reports WHERE status = 'rejected') as rejected_reports,
        (SELECT AVG(score) FROM ratings) as avg_rating
      `
    );

    return interactionStats;
  }

  // 获取时间段统计
  async getPeriodStatistics(startDate, endDate) {

    // 时间段内的用户统计
    const [userStats] = await executeQuery(
      `SELECT 
        COUNT(*) as new_users,
        COUNT(CASE WHEN status = 1 THEN 1 END) as active_users
       FROM users
       WHERE created_at BETWEEN ? AND ?`,
      [startDate, endDate]
    );

    // 时间段内的书籍统计
    const [bookStats] = await executeQuery(
      `SELECT 
        COUNT(*) as new_books,
        COUNT(DISTINCT user_id) as contributing_users
       FROM posts
       WHERE created_at BETWEEN ? AND ?`,
      [startDate, endDate]
    );

    // 时间段内的互动统计
    const [interactionStats] = await executeQuery(
      `SELECT 
        (SELECT COUNT(*) FROM likes WHERE created_at BETWEEN ? AND ?) as likes,
        (SELECT COUNT(*) FROM comments WHERE created_at BETWEEN ? AND ?) as comments,
        (SELECT COUNT(*) FROM ratings WHERE created_at BETWEEN ? AND ?) as ratings
      `,
      [startDate, endDate, startDate, endDate, startDate, endDate]
    );

    return {
      period: { startDate, endDate },
      users: userStats,
      books: bookStats,
      interactions: interactionStats
    };
  }
}

module.exports = new StatisticsService(); 